BEGIN;


-- partner_user
CREATE SEQUENCE partner_user_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

CREATE table partner_user (
	id integer not null,
	partner_id integer not null,
	username varchar(512) not null,
	password varchar(512) not null,
	salt varchar(16) not null,
	last_login timestamp not null,
	added timestamp not null,
	api_key varchar(128) not null
);

ALTER TABLE partner_user ALTER COLUMN id SET DEFAULT nextval('partner_user_id_seq'::regclass);
ALTER TABLE partner_user ADD CONSTRAINT partner_user_pk PRIMARY KEY (id);
CREATE UNIQUE INDEX username_idx ON partner_user(username);


-- sessions
CREATE TABLE sessions (
    id character varying(256) NOT NULL,
    data text,
    expires integer
);

ALTER TABLE sessions ADD CONSTRAINT sessions_pkey PRIMARY KEY (id);
CREATE UNIQUE INDEX id_idx ON sessions(id);
CREATE UNIQUE INDEX expires_idx ON sessions(expires);


-- insert data
insert into partner_user (id, partner_id, username, password, salt, last_login, added, api_key) values (1, 1, 'client', '217076dd5238e465fe105b7d6f844571737e6182', 'client', '2012-12-12 03:34:01', '2012-12-12 12:32:34', 'apikeypartner1');


-- authenticate the client, return the user Id of 0 in case of failure
CREATE OR REPLACE FUNCTION authenticateClient(username varchar, password varchar) RETURNS integer AS $$
DECLARE
	userRow partner_user%ROWTYPE;
	encodedPassword partner_user.password%TYPE;
BEGIN
	-- search for the username, exact match
	SELECT INTO userRow * FROM partner_user where username = username;

	IF NOT FOUND THEN
		RETURN 0;
	END IF;


	-- now match the password
	encodedPassword := encode(digest(password, 'sha1'), 'hex') || userRow.salt;
	encodedPassword := encode(digest(encodedPassword, 'sha1'), 'hex');

	IF (encodedPassword != userRow.password) THEN
		return 0;
	END IF;


	-- update the last login
	UPDATE partner_user SET last_login = CURRENT_TIMESTAMP WHERE id = userRow.id;

	RETURN userRow.id;
END;
$$ LANGUAGE plpgsql;


-- get the user by ID
CREATE OR REPLACE FUNCTION getUserById(userId integer) RETURNS SETOF partner_user AS $$
DECLARE
	userRow partner_user%ROWTYPE;
BEGIN
	-- search for the ID
	SELECT INTO userRow * FROM partner_user WHERE id = userId LIMIT 1;

	RETURN NEXT userRow;
END;
$$ LANGUAGE plpgsql;


-- get the session by session ID
CREATE OR REPLACE FUNCTION getSessionById(sessionId varchar) RETURNS SETOF sessions AS $$
DECLARE
	sessionRow sessions%ROWTYPE;
BEGIN
	-- search for the ID
	SELECT INTO sessionRow * FROM sessions WHERE id = sessionId LIMIT 1;

	RETURN NEXT sessionRow;
END;
$$ LANGUAGE plpgsql;


-- delete session by session ID
CREATE OR REPLACE FUNCTION deleteSessionById(sessionId varchar) RETURNS boolean AS $$
BEGIN
	DELETE FROM sessions WHERE id = sessionId;

	RETURN true;
END;
$$ LANGUAGE plpgsql;


-- create session
CREATE OR REPLACE FUNCTION createSession(sessionId varchar, sessionData varchar, sessionExpires integer) RETURNS boolean AS $$
DECLARE
	deleteResult boolean;
BEGIN
	-- make sure there's no session
	SELECT INTO deleteResult * FROM deleteSessionById(sessionId);

	INSERT INTO sessions (id, data, expires) VALUES (sessionId, sessionData, sessionExpires);

	RETURN true;
END;
$$ LANGUAGE plpgsql;


-- delete expired sessions
CREATE OR REPLACE FUNCTION deleteExpiredSession(maxLifeTime integer) RETURNS boolean AS $$
BEGIN
	DELETE FROM sessions WHERE expires < maxLifeTime;

	RETURN true;
END;
$$ LANGUAGE plpgsql;


-- reset session
CREATE OR REPLACE FUNCTION resetSession(newSessionId varchar, oldSessionId varchar) RETURNS boolean AS $$
BEGIN
	UPDATE sessions SET id = newSessionId WHERE id = oldSessionId;

	RETURN true;
END;
$$ LANGUAGE plpgsql;


COMMIT;
